-- checking to see index Fragmentation
Print 'Selecting Index Fragmentation in the database.'

SELECT 
  DB_NAME(DPS.DATABASE_ID) AS [DatabaseName]
 ,OBJECT_NAME(DPS.OBJECT_ID) AS TableName
 ,SI.NAME AS IndexName
 ,DPS.INDEX_TYPE_DESC AS IndexType
 ,DPS.AVG_FRAGMENTATION_IN_PERCENT AS AvgPageFragmentation
 ,DPS.PAGE_COUNT AS PageCounts
 ,OrigFillFactor as OrigianlFillFactor
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, NULL) DPS --N'LIMITED') DPS
INNER JOIN sysindexes SI 
    ON DPS.OBJECT_ID = SI.ID 
    AND DPS.INDEX_ID = SI.INDID
ORDER BY DPS.avg_fragmentation_in_percent DESC
GO

-- REbuild all indexes with fillfactor 80

Print 'Rebuilding indexes on every table in the database.'

EXEC sp_MSforeachtable @command1='Print "rebuilding indexes on ?"; ALTER INDEX ALL ON ? REBUILD WITH (FILLFACTOR = 80, STATISTICS_NORECOMPUTE = OFF)'
GO


alter index all on [Production].[ProductReview] rebuild with (FILLFACTOR = 80, STATISTICS_NORECOMPUTE = OFF)

EXEC sp_MSforeachtable @command1='Print "rebuilding indexes on ?"; '

SELECT 
*
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, NULL) DPS --N'LIMITED') DPS
INNER JOIN sysindexes SI 
    ON DPS.OBJECT_ID = SI.ID 
    AND DPS.INDEX_ID = SI.INDID
ORDER BY DPS.avg_fragmentation_in_percent DESC
GO
OrigFillFactor
0